package com.wendyxie.sproutup.db

import android.content.ContentValues
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import com.wendyxie.sproutup.entity.UserTreeEntity
import com.wendyxie.sproutup.utils.RandomUtil
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import java.util.*

class SQLUserTreeHelper {
    // 定义静态变量
    companion object {
        private const val TABLE_NAME = "user_tree"
        private const val KEY_USER_TREE_ID = "user_tree_id"
        private const val KEY_USER_ID = "user_id"
        private const val KEY_BABY_ID = "baby_id"
        private const val KEY_TREE_ID = "tree_id"
        private const val KEY_TREE_PRICE = "tree_price"
        private const val KEY_TREE_NAME = "tree_name"
        private const val KEY_TREE_IMAGE_URL = "tree_image_url"
        private const val KEY_EXCHANGE_TIME = "exchange_time"

        // 创建表
        fun createUserTreeTable(db: SQLiteDatabase) {
            val sql = """
                CREATE TABLE IF NOT EXISTS $TABLE_NAME (
                    $KEY_USER_TREE_ID varchar(36) not null primary key,
                    $KEY_USER_ID varchar(36),
                    $KEY_BABY_ID varchar(36),
                    $KEY_TREE_ID varchar(36),
                    $KEY_TREE_PRICE int,
                    $KEY_TREE_NAME varchar(100),
                    $KEY_TREE_IMAGE_URL varchar(100),
                    $KEY_EXCHANGE_TIME DEFAULT CURRENT_TIMESTAMP
                    )
                    """
            db.execSQL(sql)
        }

        // 添加兑换记录
        fun addUserTree(userTreeEntity: UserTreeEntity, db: SQLiteDatabase) {
            // 创建 ContentValues 对象，用于存储插入的数据
            val contentValues = ContentValues()
            contentValues.put(KEY_USER_ID, userTreeEntity.userId)
            contentValues.put(KEY_BABY_ID, userTreeEntity.babyId)
            contentValues.put(KEY_USER_TREE_ID, RandomUtil.getUUID())
            contentValues.put(KEY_TREE_ID, userTreeEntity.treeId)
            contentValues.put(KEY_TREE_PRICE, userTreeEntity.treePrice)
            contentValues.put(KEY_TREE_NAME, userTreeEntity.treeName)
            contentValues.put(KEY_TREE_IMAGE_URL, userTreeEntity.treeImageUrl)
            contentValues.put(
                KEY_EXCHANGE_TIME,
                userTreeEntity.exchangeTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
            )

            db.insertOrThrow(TABLE_NAME, null, contentValues)
        }

        // 查询所有兑换记录
        fun queryUserTreesByBabyId(babyId: String, db: SQLiteDatabase): List<UserTreeEntity> {
            val cursor = db.query(
                TABLE_NAME, null, "$KEY_BABY_ID = ?", arrayOf(babyId),
                null, null, "$KEY_EXCHANGE_TIME desc"
            )
            val userTreeList = mutableListOf<UserTreeEntity>()
            while (cursor.moveToNext()) {
                userTreeList.add(cursorToUserTreeEntity(cursor))
            }
            cursor.close()
            return userTreeList
        }

        // 根据兑换记录ID查询兑换记录
        fun queryUserTreeById(userTreeId: String, db: SQLiteDatabase): UserTreeEntity? {
            val sql = "$KEY_USER_TREE_ID = ?"
            val params = arrayOf(userTreeId)
            val orderBy = "$KEY_EXCHANGE_TIME desc"
            val cursor = db.query(TABLE_NAME, null, sql, params, null, null, orderBy)
            val userTreeList = mutableListOf<UserTreeEntity>()
            while (cursor.moveToNext()) {
                userTreeList.add(cursorToUserTreeEntity(cursor))
            }
            cursor.close()
            return userTreeList.getOrNull(0)
        }

        // 将 Cursor 转换为 UserTreeEntity
        private fun cursorToUserTreeEntity(cursor: Cursor): UserTreeEntity {
            return UserTreeEntity(
                cursor.getString(cursor.getColumnIndex(KEY_USER_ID)),
                cursor.getString(cursor.getColumnIndex(KEY_BABY_ID)),
                cursor.getString(cursor.getColumnIndex(KEY_USER_TREE_ID)),
                cursor.getString(cursor.getColumnIndex(KEY_TREE_ID)),
                cursor.getInt(cursor.getColumnIndex(KEY_TREE_PRICE)),
                cursor.getString(cursor.getColumnIndex(KEY_TREE_NAME)),
                cursor.getString(cursor.getColumnIndex(KEY_TREE_IMAGE_URL)),
                LocalDateTime.parse(
                    cursor.getString(cursor.getColumnIndex(KEY_EXCHANGE_TIME)),
                    DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
                ),
            )
        }
    }

}

